import time
import numpy as np
import pymysql 
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump
import requests
import datetime

class WeatherUtils(object):
    """
    天气类
    """
    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
        self.url = 'http://v1.yiketianqi.com/api'

    def get_data(self):
        """
        获取天气数据
        """
        date_list = []
        for d in self.date_list:
            conf = {
                'appid': '84115858 ',  
                'appsecret': 'xGLl22QM ',  
                'version': 'history',
                'type': 'month',
                'year': d[0:4],
                'month': d[5:7],
                'city': '南昌',
            }
            # 发起请求获取数据
            try:
                res = requests.get(self.url, params=conf)
                res_data = res.json()
                for i in res_data['data']:
                    date_list.append({
                        'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'),
                        'bWendu': i['bWendu'],
                        'yWendu': i['yWendu'],
                        'tianqi': i['tianqi'],
                        'fengxiang': i['fengxiang'],
                        'fengli': i['fengli'],
                    })
                print(f"成功获取 {d} 的天气数据")
            except Exception as e:
                print(f"获取 {d} 天气数据失败: {e}")
                
        df = pd.DataFrame(date_list)
        df.to_csv('weather_data.csv', index=False)
        print("天气数据已保存到 NN/weather_data.csv")
        return df

    def create_sample_weather_data(self):
        """
        创建示例天气数据（如果API失败时使用）
        """
        print("正在创建示例天气数据...")
        dates = pd.date_range(start='2024-07-01', end='2024-12-31', freq='D')
        sample_data = []

        for date in dates:
            sample_data.append({
                'date': date,
                'bWendu': f"{np.random.randint(25, 35)}°",
                'yWendu': f"{np.random.randint(15, 25)}°",
                'tianqi': np.random.choice(['晴', '多云', '阴', '小雨']),
                'fengxiang': np.random.choice(['东风', '南风', '西风', '北风']),
                'fengli': np.random.choice(['1-2级', '3-4级', '微风'])
            })

        df = pd.DataFrame(sample_data)
        df.to_csv('NN/weather_data.csv', index=False)
        print("示例天气数据已保存到 weather_data.csv")
        return df


class MysqlUtils(object):

    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host= '127.0.0.1',
            user= 'root',
            password= '123456',
            database= 'scenic2',
            port= 3306,          # 明确指定端口
            charset= 'utf8mb4'   # 添加字符集设置
        )
        
        # 检查天气数据文件是否存在，如果不存在则创建
        try:
            self.weather_df = pd.read_csv('weather_data.csv')
            print("成功加载天气数据文件")
        except FileNotFoundError:
            print("天气数据文件不存在，正在创建示例数据...")
            wu = WeatherUtils()
            self.weather_df = wu.create_sample_weather_data()
        
    def is_holiday(self, date):
        """是否节假日判断
        """
        holiday_dates = ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', 
                        '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', 
                        '2025-01-01', '2025-01-02', '2025-01-03']
        date_str = date.strftime('%Y-%m-%d') if hasattr(date, 'strftime') else str(date)
        return 1 if date_str in holiday_dates else 0
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count 
        FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date, hour
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        
        # 合并天气数据
        self.weather_df['date'] = pd.to_datetime(self.weather_df['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(df, self.weather_df, on='date')
        df_pivot.set_index('date', inplace=True)
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几(0-6)
        df_pivot['month'] = df_pivot.index.month  # 月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)

        # 独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli', 'fengxiang'], dtype=int)
        
        # 处理温度数据
        df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('°', '').astype(int)
        df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('°', '').astype(int)

        # 归一化入园数
        scaler = MinMaxScaler()
        feature = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(feature)
        dump(scaler, 'scaler.joblib')
        
        # 归一化天气
        weather_features = ['bWendu', 'yWendu']
        df_pivot[weather_features] = scaler.fit_transform(df_pivot[weather_features])
        dump(scaler, 'weather_scaler.joblib')

        df_pivot.to_csv('scenic_data.csv', index=False)
        print("数据处理完成，已保存到 NN/scenic_data.csv")
        
        # 关闭数据库连接
        self.conn.close()
        
        
def main():
    """
    主函数：按顺序执行所有步骤
    """
    print("开始执行数据预处理流程...")
    
    # 步骤1：获取天气数据
    print("\n=== 步骤1: 获取天气数据 ===")
    wu = WeatherUtils()
    try:
        weather_df = wu.get_data()
        print("✓ 天气数据获取成功")
    except Exception as e:
        print(f"✗ 天气API获取失败: {e}")
        print("正在创建示例天气数据...")
        weather_df = wu.create_sample_weather_data()
        print("✓ 示例天气数据创建成功")
    
    # 步骤2：处理数据库数据并合并天气数据
    print("\n=== 步骤2: 处理数据库和天气数据 ===")
    try:
        mu = MysqlUtils()
        mu.get_data()
        print("✓ 数据预处理完成")
    except Exception as e:
        print(f"✗ 数据处理失败: {e}")
    
    print("\n=== 所有步骤执行完成 ===")

if __name__ == '__main__':
    main()